/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package dnbW.dbW.sucxentdbW;

import dnbW.dbW.PathExpression;
import dnbW.entityW.Path;
import java.util.ArrayList;
import java.util.List;

/**
 * The job of this object is to create a SUCXENT++ query to extract the result
 * subtrees from the database based on the indexes stored in the PathFinal table.
 * The design of the PathFinal table is assumed to be (DocId, AncestorDeweyGroup).
 * @author truongbaquan
 */
public class FinalResultQueryCreator {
    /**
     * Create a query to extract the subtrees from the indexes stored in a final
     * table
     * @param outputPathExpr        The path of the root of all result subtrees
     * @param finalTableName    The name of the final table in the database
     * @return                  The query
     */
    public static String createQuery(PathExpression outputPathExpr, String finalTableName) {
        String withClause = "with FullTable(DocId, AncestorDeweyGroup, PathId, LeafValue) as (\n" +
	"select DocId, AncestorDeweyGroup, PathId, LeafValue from #DataSrc#_Attribute where PathId in #AttributePathIdList#\n" +
	"union all\n" +
	"select DocId, AncestorDeweyGroup, PathId, LeafValue from #DataSrc#_PathValue where PathId in #ElementPathIdList#)\n";
        List<Integer> elementPathIdList = new ArrayList<Integer>();
        List<Integer> attributePathIdList = new ArrayList<Integer>();

        Path outputPath = Path.buildPathFromPathExp(outputPathExpr.getDataSource(), outputPathExpr.getFullPathText());
        SucxentDatabaseEngine.getPathId(outputPath, elementPathIdList, attributePathIdList);
        withClause = withClause.replace("#ElementPathIdList#", createIdListString(elementPathIdList));
        withClause = withClause.replace("#AttributePathIdList#", createIdListString(attributePathIdList));
        withClause = withClause.replaceAll("#DataSrc#", outputPathExpr.getDataSource().getName());
        
        // create SELECT clause
        String selectClause = "SELECT FullResult.*\n";

        // create FROM clause
        StringBuilder fromClause = new StringBuilder("FROM " + finalTableName + " ID, FullTable FullResult");
        
        // create WHERE clause
         StringBuilder whereClause = new StringBuilder("WHERE ");

        // ancestor condition
        int level = outputPathExpr.getFullPath().size();
        if (outputPathExpr.getDataSource().getNumDocuments() == 1) {
            long ancestorValue = outputPathExpr.getDataSource().tryGetDocument(1).getAncestorValue(level-1);
            whereClause.append("FullResult.AncestorDeweyGroup/" + ancestorValue +
                    " = ID.AncestorDeweyGroup/" + ancestorValue + "\n");
        }
        else {
            fromClause.append(" join " + outputPathExpr.getDataSource().getName() + "_DocumentRValue R on FullResult.DocId = R.DocId");
            whereClause.append("R.Level = " + level +
                    " AND FullResult.AncestorDeweyGroup/R.AncestorValue " +
                    "= ID.AncestorDeweyGroup/R.AncestorValue\n");
        }
        fromClause.append("\n");

        return withClause + selectClause + fromClause.toString() + whereClause.toString() 
                + "order by FullResult.DocId, FullResult.AncestorDeweyGroup";
    }

    private static String createIdListString(List<Integer> idList) {
        if (idList == null || idList.isEmpty()) return "(0)";
        StringBuilder builder = new StringBuilder("(");
        boolean first = true;
        for (int id : idList) {
            if (first) {
                builder.append(id);
                first = false;
            }
            else {
                builder.append(", " + id);
            }
        }
        builder.append(")");
        return builder.toString();
    }
}
